﻿using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Linq;
using System.Web;

namespace Beryl.OrderControllers
{
    public class reWorkQuery
    {
        private static reWorkQuery instance = null;
        private static readonly object uniqueLock = new object();

        public static reWorkQuery Instance
        {
            get
            {
                lock (uniqueLock)
                {
                    if (instance == null)
                    {
                        instance = new reWorkQuery();
                    }
                }
                return instance;
            }
        }
        public reWorkQuery() { }

        //数据查询
        public List<string> OrderOrderQuery(string para)
        {
            List<string> list = new List<string>();
            try
            {
                var sql = string.Format(@"SELECT a.dDemNo,b.cMatNo AS cMatNo1,a.dMatQty,a.cMatNo,ISNULL(a.cMOLotNo,'') AS cMOLotNo,ISNULL(a.reWorkReason,'') AS reWorkReason,c.cSpecifucation,c.cMatName FROM APS_ReworkMatList a " +
 " LEFT JOIN APS_Rework b ON b.cReworkNo = a.dDemNo left join APS_MatData_V c on c.cMatNo = a.cMatNo where a.dDemNo = '{0}';",para);
                string ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                list.Add(ret);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //数据录入
        public List<string> UpdateOrder(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            List<string> listSql = new List<string>();
            try
            {
                var sql2 = "";
                var sql3 = "";
                string dDemoNo1 = "";
                for (int i = 0; i < jArray.Count; i++)
                {
                    var k = jArray[i];
                    var dDemNo = k["dDemNo"].ToString();
                    dDemoNo1 = dDemNo;
                    var dMatQty = k["dMatQty"].ToString();
                    var cMatNo = k["cMatNo"].ToString();
                    var reWorkReason = k["reWorkReason"].ToString();
                    var cMatType = "";
                    if (cMatNo.StartsWith("Y"))
                    { //根据物料编码第一位字符判断是成品还是子件  Y子件  C成品
                        cMatType = "2";
                        reWorkReason = "";
                    }

                    if (cMatNo.StartsWith("C"))
                    { //根据物料编码第一位字符判断是成品还是子件  Y子件  C成品
                        cMatType = "1";
                    }
                    var cMOLotNo = k["cMOLotNo"].ToString();
                    sql2 += string.Format(@"INSERT INTO APS_ReworkMatList(dDemNo, cMatNo, cMOLotNo, dMatQty, cMatType, reWorkReason) values " +
           "('{0}', '{1}', '{2}', '{3}', '{4}', '{5}');",dDemNo, cMatNo, cMOLotNo, dMatQty, cMatType, reWorkReason);
                }
                listSql.Add(sql2);
                sql3 += string.Format(@"DELETE FROM APS_ReworkMatList WHERE dDemNo = '{0}';",dDemoNo1);
                listSql.Add(sql3);
                bool ok = CommonMethod.Instance.insertSqlList(listSql);
                if (!ok)
                {
                    throw new Exception("数据录入插入失败");
                }
                list.Add("success");
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        public List<string> InputDataQuery(string para)
        {
            List<string> list = new List<string>();
            Dictionary<string, string> dic = new Dictionary<string, string>();
            try
            {
                var sql = "select MotypeCode,Description from APS_Motype";
                if(para.Equals("Z21") || para.Equals("Z61"))
                {
                    sql += string.Format(@" WHERE cProductDepNo = '{0}';", para);
                }
                string ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql));
                if(ret.Equals("[]"))
                {
                    throw new Exception("查询失败");
                }
                dic.Add("APS_Motype", ret);
                
                var sql1 = "SELECT DISTINCT cPRoutingNo,cIdentCode,cIdentDesc FROM APS_ReworkRouting_V ";

                switch (para)
                {
                    case "Z21":
                        sql1 += " where cIdentDesc not like '%固态%';";
                        break;
                    case "Z61":
                        sql1 += " where cIdentDesc like '%固态%';";
                        break;
                    default:
                        break;
                }
                ret = JsonConvert.SerializeObject(CommonMethod.Instance.selectDatabase(sql1));
                if(ret.Equals("[]"))
                {
                    throw new Exception("查询失败");
                }
                dic.Add("APS_ReworkRouting_V", ret);
                string jsonStr = JsonConvert.SerializeObject(dic);
                jsonStr = CommonMethod.Instance.JosnChange(jsonStr);
                list.Add(jsonStr);
            }
            catch(Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }

        //重工单订单修改
        public List<string> ReWorkUpdate(string para)
        {
            List<string> list = new List<string>();
            Newtonsoft.Json.Linq.JArray jArray = (Newtonsoft.Json.Linq.JArray)JsonConvert.DeserializeObject(para);
            Dictionary<string, string> pairs = null;
            List<Dictionary<string, string>> listArr = new List<Dictionary<string, string>>();
            List<string> listSql = new List<string>();
            try
            {
                var k = jArray[0];
                string cReworkMOLotNo = k["cReworkMOLotNo"].ToString();
                string cReworkNo = k["cReworkNo"].ToString();
                string cMatNo = k["cMatNo"].ToString();
                string dQty = k["dQty"].ToString();
                string MotypeCode = k["MotypeCode"].ToString();
                string Description = k["Description"].ToString();
                string cProRoutingNo = k["cProRoutingNo"].ToString();
                string[] processArr = k["processArr"].ToString().Split('-');

                //删除子件物料
                var deleteSql = string.Format(@"delete from APS_ReworkMatList where dDemNo = '{0}' and cMatType = '2';", cReworkNo);

                //根据工艺路线带出子件物料
                var querySql = string.Format(@"select 成品编码, 子件物料编码, 存货大类编码, 分子用量, 分母用量, 子件损耗率 from[dbo].[View_Bom] where 成品编码 = '{0}'; ",cMatNo);
                DataRow[] row = CommonMethod.Instance.selectDatabase(querySql).Select("1 = 1");
                if(row.Length == 1)
                {
                    var matno = row[0][1].ToString();
                    var querySql1 = string.Format(@"select 成品编码, 子件物料编码, 存货大类编码, 分子用量, 分母用量, 子件损耗率 from[dbo].[View_Bom] where 成品编码 = '{0}';",matno);
                    DataRow[] row1 = CommonMethod.Instance.selectDatabase(querySql1).Select("1 = 1");
                    if(row1.Length > 0)
                    {
                        for(int i = 0; i < row1.Length; i++)
                        {
                            var item1 = row1[i];
                            if (item1[2].ToString() == "Y0112" || item1[2].ToString() == "Y0118" || item1[2].ToString() == "Y0119")
                            {
                                pairs = new Dictionary<string, string>();
                                string cMatNoN = item1[1].ToString(); //子件物料编码
                                string cCateNo = item1[2].ToString(); //存货大类编码
                                string dLength = item1[3].ToString(); //分子用量
                                string subQty = item1[4].ToString(); //分母用量
                                string compScrap = item1[5].ToString(); //子件损耗率
                                pairs.Add("cMatNoN", cMatNoN);
                                pairs.Add("cCateNo", cCateNo);
                                pairs.Add("dLength", dLength);
                                pairs.Add("subQty", subQty);
                                pairs.Add("compScrap", compScrap);
                                listArr.Add(pairs);
                            }
                        }
                    }
                }

                if(row.Length > 1)
                {
                    for(int i = 0; i < row.Length; i++)
                    {
                        var item2 = row[i];
                        pairs = new Dictionary<string, string>();
                        string cMatNoN = item2[1].ToString(); //子件物料编码
                        string cCateNo = item2[2].ToString(); //存货大类编码
                        string dLength = item2[3].ToString(); //分子用量
                        string subQty = item2[4].ToString(); //分母用量
                        string compScrap = item2[5].ToString(); //子件损耗率

                        pairs.Add("cMatNoN", cMatNoN);
                        pairs.Add("cCateNo", cCateNo);
                        pairs.Add("dLength", dLength);
                        pairs.Add("subQty", subQty);
                        pairs.Add("compScrap", compScrap);
                        listArr.Add(pairs);
                    }
                }

                var insertSql = "";
                for (int j = 0; j < processArr.Length; j++)
                {
                    var processName = processArr[j];
                    if (processName == "剥铝壳")
                    {
                        foreach(var listT in listArr)
                        {
                            if (listT["cCateNo"].ToString() == "Y0118" || listT["cCateNo"].ToString() == "Y0112" || listT["cCateNo"].ToString() == "Y0119")
                            {
                                string matNo = listT["cMatNo"].ToString();
                                string dLength = listT["dLength"].ToString();
                                string subQty = listT["subQty"].ToString();
                                string compScrap = listT["compScrap"].ToString();
                                double matQty = (float.Parse(dLength) / float.Parse(subQty)) * (1 + float.Parse(compScrap) * 0.01) * float.Parse(dQty);
                                insertSql +=  string.Format(@"insert into APS_ReworkMatList(dDemNo, cMatNo, dMatQty, cMatType) values('{0}', '{1}', '{2}', '2'); ", cReworkNo, matNo, matQty);
                            }
                        }
                    }

                    if(processName == "剥胶")
                    {
                        foreach (var listT in listArr)
                        {
                            if (listT["cCateNo"].ToString() == "Y0119")
                            {
                                string matNo = listT["cMatNo"].ToString();
                                string dLength = listT["dLength"].ToString();
                                string subQty = listT["subQty"].ToString();
                                string compScrap = listT["compScrap"].ToString();
                                double matQty = (float.Parse(dLength) / float.Parse(subQty)) * (1 + float.Parse(compScrap) * 0.01) * float.Parse(dQty);
                                insertSql += string.Format(@"insert into APS_ReworkMatList(dDemNo, cMatNo, dMatQty, cMatType) values('{0}', '{1}', '{2}', '2'); ", cReworkNo, matNo, matQty);
                            }
                        }
                    }
                }

                var sqlp =  string.Format(@"UPDATE APS_Rework SET cMatNo = '{0}', dQty = '{1}',cReworkMOLotNo = '{2}',MotypeCode = '{3}'," +
                            "Description = '{4}',cProRoutingNo = '{5}' WHERE cReworkNo = '{6}';", cMatNo, dQty, cReworkMOLotNo, MotypeCode, Description, cProRoutingNo, cReworkNo);
                listSql.Add(deleteSql);
                listSql.Add(insertSql);
                listSql.Add(sqlp);
                bool ok = CommonMethod.Instance.insertSqlList(listSql);
                if (!ok)
                {
                    throw new Exception("重工单订单修改失败");
                }
                list.Add("success");
            }
            catch (Exception e)
            {
                list.Add(e.Message);
            }
            return list;
        }
    }
}